package data;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.swing.JOptionPane;

import ui.MessagePopUp;
import controller.ATM;

/** Represents the bankDatabase for all the data
 * containing DTO, the arraylist and DAOs, the 
 * DBMS related methods 
 * @author Matthias
 */
public class BankDatabase {

	/** Declaring the ATM object for type atm **/
	private final ATM atm;
	/** Declaring RegexInput object for type regexInput **/
	private final RegexInput regexInput;
	/** Arraylist of User **/
	private final ArrayList<User> userList; 
	
	/** Constant of the URL for DBMS access **/
	private static final String SQL_URL = "jdbc:mysql://localhost/dbatm";
	/** Constant of the username for DBMS access **/
	private static final String SQL_USERNAME = "root";
	/** Constant of the password for DBMS access **/
	private static final String SQL_PASSWORD = "";
	/** Constant of the driver for DBMS access **/
	private static final String SQL_JDBCDRIVER = "com.mysql.jdbc.Driver";

	/** Constructor of BankDatabase
	 * @param atm is ATM object
	 * @param regexInput is RegexInput object
	 */
	public BankDatabase(final ATM atm, final RegexInput regexInput){

		this.atm = atm;
		this.regexInput = regexInput;
		this.userList = new ArrayList<User>();
		
		// calling the methods, to establish the SQL environment
		try {
			createDB();
			getDBConnection();
			createUserTable();
			createAccountTable();
			populateSQLUserTable();
			populateSQLAccountTable();
		} catch (SQLException e) {
			String text = "SQLException occured in your SQL DBMS, " 
					+ "establishing the DB environment. Please try again";
			new MessagePopUp().setError(text);
		} catch (ClassNotFoundException e) {
			String text = "ClassNorfoundException occured, " 
					+ "establishing the DB environment. Please try again";
			new MessagePopUp().setError(text);
		}
	}
	
	////////////////////////// Establishing the SQL environment ////////////////////
	////////////////////////////////////////////////////////////////////////////////
	////////////////////////// Start MySQL statements / methods ////////////////////
	
	/** Creating the database dbatm, in case its not existing
	 * @throws SQLException if sql syntax is wrong
	 * @throws ClassNotFoundException if connection fails
	 */
	private static void createDB() throws SQLException, ClassNotFoundException {
		
		final String dbURL = "jdbc:mysql://localhost/";

		Statement stmt = null;
		Connection conn = null;
		String dbSql = "CREATE DATABASE dbatm";

		try {
			Class.forName(SQL_JDBCDRIVER);
			conn = DriverManager.
					getConnection(dbURL, SQL_USERNAME, SQL_PASSWORD);
			stmt = conn.createStatement();
			stmt.executeUpdate(dbSql);
		}
		// no catch-statements, in order to avoid an error message.
		// The database will at restarts still exist
		catch (SQLException exc) {} 
		catch (ClassNotFoundException exc2) {}
		finally {
			stmt.close();
			conn.close();
		}
	}

	/** Connecting the database atm with the program
	 * @return the connection to the database
	 */
	public static Connection getDBConnection() {

		Connection conn = null;

		try {
			// Register JDBC driver
			Class.forName(SQL_JDBCDRIVER);
		} 
		catch (ClassNotFoundException e) {
			JOptionPane.showMessageDialog(null, e.getMessage(), 
					"Error", JOptionPane.WARNING_MESSAGE);
		}
		try {
			// Open a connection
			conn = DriverManager.getConnection(
					SQL_URL, SQL_USERNAME, SQL_PASSWORD);
		}
		catch (SQLException e) {
			String text = "Problems occured connecting the database, " 
					+ "please call an admin for help";
			new MessagePopUp().setError(text);
		}
		return conn;
	}
	
	/** Creating the db_user table for the database. 
	 * The table stores all user data
	 * @throws SQLException in case a SQL errors occurs 
	 */
	private static void createUserTable() throws SQLException {

		Connection conn = getDBConnection();
		Statement stmt = conn.createStatement();
		String createUserTableSQL = "CREATE TABLE DB_USER("
				+ "User_ID CHAR(8) NOT NULL, "
				+ "PIN INT NOT NULL, "
				+ "Firstname VARCHAR(30) NOT NULL, "
				+ "Lastname VARCHAR(30) NOT NULL, "
				+ "Telephone_Number VARCHAR(15) NOT NULL, "
				+ "Streetname VARCHAR(30) NOT NULL, "
				+ "Streetnr VARCHAR(30) NOT NULL, "
				+ "Zipcode VARCHAR(5) NOT NULL, "
				+ "City VARCHAR(30) NOT NULL, "
				+ "Created_Date DATETIME NOT NULL, "
				+ "PRIMARY KEY (User_ID)) ";

		try {
			stmt.executeUpdate(createUserTableSQL);
		} 
		// no catch-statements, in order to avoid an error message.
		// The table will at restarts still exist 
		catch (SQLException exc) {} 
		finally {
			stmt.close();
			conn.close();
		}
	}
	
	/** Creating the db_account table for the database. 
	 * The table stores all account data
	 * @throws SQLException in case a SQL errors occurs 
	 */
	private static void createAccountTable() throws SQLException {

		Connection conn = getDBConnection();
		Statement stmt = conn.createStatement();

		String createAccountTableSQL = "CREATE TABLE DB_ACCOUNT("
				+ "User_ID CHAR(8) NOT NULL, "
				+ "Account_Number INT NOT NULL, "
				+ "Account_Type VARCHAR(30) NOT NULL, "
				+ "Interest_Rate VARCHAR(30) NOT NULL, "
				+ "Total_Balance DOUBLE, "
				+ "Created_Date DATETIME NOT NULL, "
				+ "PRIMARY KEY (Account_Number), "
				+ "FOREIGN KEY (User_ID) REFERENCES DB_USER (User_ID), "
				+ "CONSTRAINT uc_User UNIQUE (User_ID, Account_Number)) ";

		try {
			stmt.execute(createAccountTableSQL);
		} 
		// no catch-statements, in order to avoid an error message.
		// The table will at restarts still exist
		catch (SQLException exc) {} 
		finally {
			stmt.close();
			conn.close();
		}
	}
	
	
	 
	/** Adding all existing users from the database to the program
	 * @throws SQLException in case a SQL errors occurs 
	 * @throws NumberFormatException in case the required number has wrong format
	 */
	private void populateSQLUserTable() throws SQLException, NumberFormatException {
		
		Connection conn = getDBConnection();
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		String selectSQL = "SELECT * FROM DB_User";
		
		try {
			preparedStatement = conn.prepareStatement(selectSQL);
			rs = preparedStatement.executeQuery(selectSQL);
			
			while (rs.next()) {
				String userID = rs.getString("User_ID");
				int pin = rs.getInt("PIN");
				String firstName = rs.getString("Firstname");
				String lastName = rs.getString("Lastname");
				String telephoneNr = rs.getString("Telephone_Number");
				String streetName = rs.getString("Streetname");
				String streetNr = rs.getString("Streetnr");
				String zipCode = rs.getString("Zipcode");
				String city = rs.getString("City");
				
				int id = Integer.parseInt(userID);
				// add all user from SQL to arraylist userList
				addUser(new User(id, pin, firstName, lastName, telephoneNr,
						streetName, streetNr, zipCode, city));			
			}
		} 
		catch (Exception e) {
			String text = "Could not populate the database, please call the admin for help";
			new MessagePopUp().setError(text);
		} 
		finally {			
			preparedStatement.close();
			conn.close();
			rs.close();
		}	
	}
			
	/** Adding all existing accounts from the database to the program
	 * @throws SQLException in case a SQL errors occurs
	 * @throws NumberFormatException in case the required number has wrong format
	 */
	public final void populateSQLAccountTable() throws SQLException, NumberFormatException {

		Connection conn = getDBConnection();
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		String selectSQL = "SELECT * FROM DB_account";

		try {
			preparedStatement = conn.prepareStatement(selectSQL);
			rs = preparedStatement.executeQuery(selectSQL);
			
			while (rs.next()) {
				String userID = rs.getString("User_ID");
				int acccountNumber = rs.getInt("Account_Number");
				String accountType = rs.getString("Account_Type");
				String interestRate = rs.getString("Interest_Rate");
				double totalBalance = rs.getDouble("Total_Balance");
				
				int id = Integer.parseInt(userID);
				// add all user from SQL to arraylist accountList
				getUsers(id).getAccountList().
				add(new Account(acccountNumber, accountType, 
						interestRate, totalBalance));
			}
		} 
		catch (SQLException e) {
			String text = "Could not populate the database, " 
					+ "please call the admin for help";
			new MessagePopUp().setError(text);
		} 
		finally {			
			preparedStatement.close();
			conn.close();
			rs.close();
		}	
	}
	
	/** Gets the current time stamp for the column DATE_TIME
	 * in the database
	 * @return the time and date of an action
	 */
	public static java.sql.Timestamp getCurrentTimeStamp() {
		java.util.Date today = new java.util.Date();
		return new java.sql.Timestamp(today.getTime());
	}
	
	/////////////////////////////// END MySQL Statements /////////////////////////
	//////////////////////////////////////////////////////////////////////////////
	///////////////////////// non-SQL methods ////////////////////////////////////
	
	/** Retrieves User object containing specified userID
	 * @param userID id of a user
	 * @return the id of the current user
	 */
	public final User getUsers(final int userID) {
		// loop through accounts searching for matching cpr_id number
		for (User currentUserID : userList) {
			// return current account if match found
			if (currentUserID.getUserID() == userID) {
				return currentUserID;
			}
		}
		// if no matching account was found, return null
		return null; 
	}

	/** Determine whether user-specified userID number and pin match 
	 * @param userID id of a user
	 * @param pin pin of a user
	 * @return the result of the validation of the authentication 
	 * either true / false.
	 */
	public final boolean authenticateUser(final int userID, final int pin) {
		// attempt to retrieve the account with the account number
		User currentUserID = getUsers(userID);
		// if account exists, return result of Account method validatePIN
			if (currentUserID != null)  {
				if (currentUserID.validatePIN(pin)) {		
				}
				return currentUserID.validatePIN(pin);
			} else {
				// account number not found, so return false
				return false;
			} 
	}

	/** Adding a user to the arraylist
	 * @param _account is an Object of User
	 */
	public final void addUser(final User _account) {
		this.userList.add(_account);
	}
	
	/** Deleting a user from the arraylist userList
	 * @param userID id of a user
	 */
	public final void deleteUser(final int userID) {
		// gets a username from the arraylist if exists in arraylist
		User user = getUsers(userID); 
		// if user isn't null - go on
		if (user != null) { 
			// if username isn't an admin - 
			// delete enabled (admin cannot be deleted)
			if (!user.toAdmin) {
				// removes the current username from the arraylist
				userList.remove(user); 
			}
		}
	}
 		
	/** Retrieving the matching interest rate of the account type
	 * @param accountType is defining which accounttype the account holds
	 * @return the matching interestrate of the parameter
	 */
	public final String getInterestRate(final String accountType) {
		String interestRate = "";
		if (accountType.equals("Credit")) {
			interestRate = "1.25";
		}
		else if (accountType.equals("Savings")) {
			interestRate = "2.50";
		}
		else if (accountType.equals("Depot")) {
			interestRate = "4.50";
		}
		return interestRate;
	}

	/** return total balance of an account with specified account number. 
	 * @param userID id of user
	 * @return the totalbalance of an accounts
	 */
	public final double getTotalBalance (final int userID) {
		return getUsers(userID).getAccountList().
				get(atm.getClassScreen().getChoiceAcc().
						getComboBox().getSelectedIndex()).getTotalBalance();
	}
	
	/** return total balance of an account in order to prevent delete of account. 
	 * For UserMenuDelete specifically.
	 * @param userID id of user
	 * @return total balance of an account
	 */
	public final double getUserMenuDelTotalBalance (final int userID) {
		return getUsers(userID).getAccountList().
				get(atm.getClassScreen().getUserMenuDelete().
						getComboBox().getSelectedIndex()).getTotalBalance();
	}

	/** credit an amount to Account with specified account number
	 * @param userID id of user
	 * @param depositAmount the entered amount in AccDeposit
	 */
	public final void credit(final int userID, final double depositAmount) {
		getUsers(userID).getAccountList().
		get(atm.getClassScreen().getChoiceAcc().
				getComboBox().getSelectedIndex()).credit(depositAmount);
	}

	/** Debit an amount from Account with specified account number
	 * @param userID id of user
	 * @param withdrawalAmount the entered amount in AccWithdrawal
	 */
	public final void debit(final int userID, final double withdrawalAmount) {
		getUsers(userID).getAccountList().
		get(atm.getClassScreen().getChoiceAcc().
				getComboBox().getSelectedIndex()).debit(withdrawalAmount);
	}
	
	/** Access to the arraylist userList object
	 * @return arraylist of User
	 */
	public ArrayList<User> getUserList() {
		return userList;
	}
	
	/** Converting the attributes of the userList to a single array
	 * @return converted info from userList in an array
	 */
	public final String[] getUserChanger() {
		// string array of userList size
		String[] info = new String[userList.size()];
		// looping with a for loop, to call attributes 
	    for (int i = 0; i < userList.size(); i++) {
	    	info[i] = "<html><p>UserID: " + userList.get(i).getUserID() 
	    			+ " - " + userList.get(i).getLastName()
					+ " <br> Accounts: " + userList.get(i).getAccountList().size() + "<hr></p></html>";
	    	// adding "Admin" to the admin specifically
	    	if (userList.get(i).getUserID() == 1234) {
	    		info[i] = "<html><p>UserID: " + userList.get(i).getUserID() 
		    			+ " - " + userList.get(i).getLastName()
						+ " <br> Accounts: " + userList.get(i).getAccountList().size() 
						+ " - "+ "Admin" +"<hr></p></html>";
	    	}
		}
	    return info;
	}
	
	/** Access to the User object. Used for the classes, which can't access the current userID
	 * @return User object
	 * 
	 */
	public final User getClassUser() {
		User users = getUsers(atm.getClassScreen().
				getWelcome().getLoginUserID());
		return users;
	}
	
	/** Access to the ATM object
	 * @return ATM object
	 */
	public final ATM getClassATM() {
		return atm;
	}
	
	/** Access to the RegexInput object
	 * @return RegexInput object
	 */
	public final RegexInput getClassRegexInput() {
		return regexInput;
	}
	
} // end class BankDatabase